﻿CREATE TABLE [Lit_Estado](
	[Id] [int] NOT NULL PRIMARY KEY,	
	[Estado] [varchar](20) NOT NULL)

INSERT INTO [Lit_Estado] VALUES (1, 'Abierta')
INSERT INTO [Lit_Estado] VALUES (2, 'En Proceso')
INSERT INTO [Lit_Estado] VALUES (3, 'Cerrada')
INSERT INTO [Lit_Estado] VALUES (4, 'Anulada')

GO

-------------------------------------------   Lit_OT -------------------------

IF  EXISTS (SELECT * FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'Lit_OT')
BEGIN
	IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTEntrega' AND CONSTRAINT_NAME = 'FK_Lit_OTEntrega_Lit_OT')
		ALTER TABLE [dbo].[Lit_OTEntrega] DROP CONSTRAINT [FK_Lit_OTEntrega_Lit_OT]

	IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OT' AND CONSTRAINT_NAME = 'PK_Lit_OT')
		ALTER TABLE [dbo].[Lit_OT] DROP CONSTRAINT [PK_Lit_OT]
				
	DROP TABLE [dbo].[Lit_OT]		
END	

CREATE TABLE [dbo].[Lit_OT](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmpresaId] [int] NULL,
	[NumeroOT] [varchar](30) NULL,
	[ArticuloId] [varchar](13) NULL,
	[ClienteId] [varchar](12) NULL,
	[FechaEntrega] [datetime] NULL,
	[FechaIngreso] [datetime] NOT NULL,
	[FechaInicial] [datetime] NULL,
	[FechaFinal] [datetime] NULL,
	[Referencia] [varchar](500) NULL,
	[VendedorId] [int] NULL,
	[PruebaColor] [bit] NULL,
	[Laser] [bit] NULL,
	[Cromacheck] [bit] NULL,
	[TrabajoNuevo] [bit] NULL,
	[ReimpresionCambios] [bit] NULL,
	[ReimpresionExacta] [bit] NULL,
	[Cantidad] [decimal](10, 2) NULL,
	[TamanoFinal] [decimal](10, 2) NULL,
	[Impresion2Lados] [bit] NULL,
	[FullColor] [bit] NULL,
	[DosColores] [bit] NULL,
	[UnColor] [bit] NULL,
	[Pantone] [bit] NULL,
	[Otros] [bit] NULL,
	[AcabadosFinales] [varchar](500) NULL,
	[Comentarios] [varchar](500) NULL,
	[Factura] [varchar](100) NULL,
	[HorasAcabados] [decimal](10, 2) NULL,
	[HorasPrensas] [decimal](10, 2) NULL,
	[GastosIndirectos] [decimal](10, 2) NULL,
	[CantidadDommies] [int] NULL,
	[Observaciones] [varchar](500) NULL,
	[Boleta] [varchar](50) NULL,
	[VistoBuenoMaquina] [bit] NULL,
	[DibujoMontaje] [varchar](100) NULL,
	[EstadoId] [int] NOT NULL,
 CONSTRAINT [PK_Lit_OT] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
)

GO

ALTER TABLE [dbo].[Lit_OT]  WITH CHECK ADD  CONSTRAINT [FK_Lit_OT_Lit_Estado] FOREIGN KEY([EstadoId])
REFERENCES [dbo].[Lit_Estado] ([Id])
GO

ALTER TABLE [dbo].[Lit_OT] CHECK CONSTRAINT [FK_Lit_OT_Lit_Estado]
GO


---------------------------------------   Lit_OTMaterialCotizado -----------------------
CREATE TABLE [dbo].[Lit_OTMaterialCotizado](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OTId] [int] NULL,
	[ArticuloId] [varchar](13) NULL,
	[Papel] [varchar](255) NULL,
	[TamanoPliegoMaterial] [varchar](50) NULL,
	[TamanoPliegoPrensa] [varchar](50) NULL,
	[Cantidad] [decimal](10, 2) NULL,
	[Aumento] [decimal](10, 2) NULL,
	[PliegosTotal] [decimal](10, 2) NULL,
	[PliegosPrensaTotal] [decimal](10, 2) NULL,
	[NoCotizado] [decimal](10, 2) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Lit_OTMaterialCotizado]  WITH CHECK ADD  CONSTRAINT [FK_Lit_OTMaterialCotizado_Lit_OT] FOREIGN KEY([OTId])
REFERENCES [dbo].[Lit_OT] ([Id])
GO

ALTER TABLE [dbo].[Lit_OTMaterialCotizado] CHECK CONSTRAINT [FK_Lit_OTMaterialCotizado_Lit_OT]
GO

-------------------------------------------   Lit_OTEntrega -------------------------

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Lit_OrdenTrabajo') 
	EXEC sp_rename 'Lit_OrdenTrabajo', 'Lit_OTEntrega'	
GO

-- Add FK column in Entrega to point to OT
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_OTEntrega' AND COLUMN_NAME = 'OTId')
   ALTER TABLE Lit_OTEntrega ADD OTId INT
GO

-- Add Order columns to Entrega table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_OTEntrega' AND COLUMN_NAME = 'Orden')
   ALTER TABLE Lit_OTEntrega ADD Orden INT
GO

-- Rename FK columns in child tables
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_OTGasto' AND COLUMN_NAME = 'OTId')
   EXEC sp_rename 'Lit_OTGasto.OTId', 'OTEntregaId', 'COLUMN'

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_OTMaterial' AND COLUMN_NAME = 'OTId')
   EXEC sp_rename 'Lit_OTMaterial.OTId', 'OTEntregaId', 'COLUMN'

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_OTDetalle' AND COLUMN_NAME = 'OTId')
   EXEC sp_rename 'Lit_OTDetalle.OTId', 'OTEntregaId', 'COLUMN'

   
-- Rename FK Constraints
IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTEntrega' AND CONSTRAINT_NAME = 'FK_Lit_RegistroOT_Lit_RegistroOT')			
	ALTER TABLE [dbo].[Lit_OTEntrega] DROP CONSTRAINT [FK_Lit_RegistroOT_Lit_RegistroOT]

IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTEntrega' AND CONSTRAINT_NAME = 'FK_Lit_OTEntrega_Lit_OT')			
	ALTER TABLE [dbo].[Lit_OTEntrega] DROP CONSTRAINT [FK_Lit_OTEntrega_Lit_OT]

ALTER TABLE [dbo].[Lit_OTEntrega]  WITH CHECK 
	ADD CONSTRAINT [FK_Lit_OTEntrega_Lit_OT] FOREIGN KEY([OTId]) 
		REFERENCES [dbo].[Lit_OT] ([Id])
ALTER TABLE [dbo].[Lit_OTEntrega] CHECK CONSTRAINT [FK_Lit_OTEntrega_Lit_OT]
GO

-- Add EstadoId columns to Entrega table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_OTEntrega' AND COLUMN_NAME = 'FechaEntrega')
   ALTER TABLE Lit_OTEntrega ADD FechaEntrega DATETIME
GO

-- Add EstadoId columns to Entrega table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_OTEntrega' AND COLUMN_NAME = 'EstadoId')
   ALTER TABLE Lit_OTEntrega ADD EstadoId INT
GO

UPDATE Lit_OTEntrega
SET EstadoId =  CASE Estado WHEN 'A' THEN 2 -- En Proceso
							WHEN 'T' THEN 3	-- Cerrada
				END

ALTER TABLE Lit_OTEntrega DROP COLUMN Estado
GO

ALTER TABLE [dbo].[Lit_OTEntrega]  WITH CHECK 
	ADD CONSTRAINT [FK_Lit_OTEntrega_Lit_Estado] FOREIGN KEY([EstadoId]) 
		REFERENCES [dbo].[Lit_Estado] ([Id])
ALTER TABLE [dbo].[Lit_OTEntrega] CHECK CONSTRAINT [FK_Lit_OTEntrega_Lit_Estado]
GO

------------------------------------------- Tablas detalle  -------------------------

IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTDetalle' AND CONSTRAINT_NAME = 'FK_lit_registroOTDetalle_Lit_RegistroOT')			
	ALTER TABLE [dbo].[Lit_OTDetalle] DROP CONSTRAINT [FK_lit_registroOTDetalle_Lit_RegistroOT]

IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTDetalle' AND CONSTRAINT_NAME = 'FK_lit_OTDetalle_Lit_OTEntrega')			
	ALTER TABLE [dbo].[Lit_OTDetalle] DROP CONSTRAINT [FK_lit_OTDetalle_Lit_OTEntrega]

ALTER TABLE [dbo].[Lit_OTDetalle]  WITH CHECK 
	ADD CONSTRAINT [FK_lit_OTDetalle_Lit_OTEntrega] FOREIGN KEY([OTEntregaId])
		REFERENCES [dbo].[Lit_OTEntrega] ([Id])
ALTER TABLE [dbo].[Lit_OTDetalle] CHECK CONSTRAINT [FK_lit_OTDetalle_Lit_OTEntrega]
GO

IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTGasto' AND CONSTRAINT_NAME = 'FK_Lit_OTGasto_Lit_OrdenTrabajo')			
	ALTER TABLE [dbo].[Lit_OTGasto] DROP CONSTRAINT [FK_Lit_OTGasto_Lit_OrdenTrabajo]

IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTGasto' AND CONSTRAINT_NAME = 'FK_Lit_OTGasto_Lit_OTEntrega')			
	ALTER TABLE [dbo].[Lit_OTGasto] DROP CONSTRAINT [FK_Lit_OTGasto_Lit_OTEntrega]

ALTER TABLE [dbo].[Lit_OTGasto]  WITH CHECK 
	ADD CONSTRAINT [FK_Lit_OTGasto_Lit_OTEntrega] FOREIGN KEY([OTEntregaId])
		REFERENCES [dbo].[Lit_OTEntrega] ([Id])

ALTER TABLE [dbo].[Lit_OTGasto] CHECK CONSTRAINT [FK_Lit_OTGasto_Lit_OTEntrega]
GO

IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTMaterial' AND CONSTRAINT_NAME = 'FK_Lit_OTMaterial_Lit_OTEntrega')					
	ALTER TABLE [dbo].[Lit_OTMaterial] DROP CONSTRAINT [FK_Lit_OTMaterial_Lit_OTEntrega]

ALTER TABLE [dbo].[Lit_OTMaterial]  WITH CHECK 
	ADD CONSTRAINT [FK_Lit_OTMaterial_Lit_OTEntrega] FOREIGN KEY([OTEntregaId])
		REFERENCES [dbo].[Lit_OTEntrega] ([Id])

ALTER TABLE [dbo].[Lit_OTMaterial] CHECK CONSTRAINT [FK_Lit_OTMaterial_Lit_OTEntrega]


IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTDetalle' AND CONSTRAINT_NAME = 'FK_Lit_OTDetalle_Lit_Operario')			
	ALTER TABLE [dbo].[Lit_OTDetalle] DROP CONSTRAINT [FK_Lit_OTDetalle_Lit_Operario]

ALTER TABLE [dbo].[Lit_OTDetalle]  WITH CHECK 
	ADD CONSTRAINT [FK_Lit_OTDetalle_Lit_Operario] FOREIGN KEY([AyudanteId])
		REFERENCES [dbo].[Lit_Operario] ([Id])

ALTER TABLE [dbo].[Lit_OTDetalle] CHECK CONSTRAINT [FK_Lit_OTDetalle_Lit_Operario]


IF  EXISTS (SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Lit_OTDetalle' AND CONSTRAINT_NAME = 'FK_Lit_OTDetalle_Lit_Maquina')			
	ALTER TABLE [dbo].[Lit_OTDetalle] DROP CONSTRAINT [FK_Lit_OTDetalle_Lit_Maquina]

ALTER TABLE [dbo].[Lit_OTDetalle]  WITH CHECK 
	ADD CONSTRAINT [FK_Lit_OTDetalle_Lit_Maquina] FOREIGN KEY([MaquinaId])
		REFERENCES [dbo].[Lit_Maquina] ([Id])

ALTER TABLE [dbo].[Lit_OTDetalle] CHECK CONSTRAINT [FK_Lit_OTDetalle_Lit_Maquina]


-------------------------------- Lit_Operario ---------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_Operario' AND COLUMN_NAME = 'Activo')
   ALTER TABLE Lit_Operario ADD Activo BIT
GO

UPDATE Lit_Operario
SET Activo = 1
WHERE Activo IS NULL
GO

-------------------------------- Lit_Puesto ---------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_Puesto' AND COLUMN_NAME = 'Activo')
   ALTER TABLE Lit_Puesto ADD Activo BIT
GO
UPDATE Lit_Puesto
SET Activo = 1
WHERE Activo IS NULL
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Lit_Puesto' AND COLUMN_NAME = 'EmpresaId')
   ALTER TABLE Lit_Puesto ADD EmpresaId INT
GO
DECLARE @empresaId INT
SELECT @empresaId = Id 
FROM ld_general.dbo.empresas
WHERE charindex('Litograf', nombre, 0) > 0
UPDATE Lit_Puesto
SET EmpresaId = @empresaId
WHERE EmpresaId IS NULL
GO

------------------------------------------- Viejos SPs  -------------------------

IF  EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'RegistroOTGet')
DROP PROCEDURE [dbo].[RegistroOTGet]

IF  EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'RegistroOTSave')
DROP PROCEDURE [dbo].[RegistroOTSave]

IF  EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'RegistroOTMaterialSave')
DROP PROCEDURE [dbo].[RegistroOTMaterialSave]

IF  EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'RegistroOTResumenMultipleRep')
DROP PROCEDURE [dbo].[RegistroOTResumenMultipleRep]

IF  EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'RegistroOTDetalleSave')
DROP PROCEDURE [dbo].[RegistroOTDetalleSave]

IF  EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'RegistroOTGastoSave')
DROP PROCEDURE [dbo].[RegistroOTGastoSave]

IF  EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'OrdenTrabajoParametersGet')
DROP PROCEDURE [dbo].[OrdenTrabajoParametersGet]

IF  EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'RegistroOTResumenRep')
DROP PROCEDURE [dbo].[RegistroOTResumenRep]

IF  EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'RegistroOTResumenMultipleRep')
DROP PROCEDURE [dbo].[RegistroOTResumenMultipleRep]
